﻿using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using StockTrader.DAL.IDAL;
using StockTrader.DAL.MySQLHelper;
using StockTrader.BusinessService.DataContract;

namespace StockTrader.DAL.MySQL
{
    public class Securities : ISecurities
    {
        private MySqlConnection _internalConnection;
        private MySqlTransaction _internalADOTransaction = null;

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new MySqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }


        #region  SQLstring

        private static string _InsertDeList = "INSERT INTO _DELIST (StockNo, StockSymbol, StockType, StockName, SectorNo) VALUES (@StockNo, @StockSymbol, @StockType, @StockName, @SectorNo) ON DUPLICATE KEY UPDATE StockNo=@StockNo, StockSymbol=@StockSymbol, StockType=@StockType, StockName=@StockName, SectorNo=@SectorNo;";
        private static string _CountDeList = "SELECT COUNT(*) FROM _DELIST;";
        private static string _DeleteDeList = "DELETE FROM _DELIST;";

        private static string _InsertFroomOrigin = "INSERT INTO _FROOM_ORIGIN (StockNo, TotalRoom, CurrentRoom) VALUES (@StockNo, @TotalRoom, @CurrentRoom) ON DUPLICATE KEY UPDATE StockNo=@StockNo, TotalRoom=@TotalRoom,CurrentRoom= @CurrentRoom;";
        private static string _CountFroomOrigin = "SELECT COUNT(*) FROM _FROOM_ORIGIN;";
        private static string _DeleteFroomOrigin = "DELETE FROM _FROOM_ORIGIN;";

        private static string _DeleteFroom = "DELETE FROM _FROOM;";
        private static string _UpdateFroom = "INSERT INTO _FROOM (StockNo, TotalRoom, CurrentRoom, Buy, Sell) VALUES (@StockNo, @TotalRoom, @CurrentRoom, @Buy, @Sell) ON DUPLICATE KEY UPDATE StockNo=@StockNo, TotalRoom=@TotalRoom, CurrentRoom=@CurrentRoom, Buy=@Buy, Sell=@Sell;";

        private static string _InsertLe = "INSERT INTO _LE (StockNo, Price, AccumulatedVol, AccumulatedVal, Highest, Lowest, Time) VALUES (@StockNo, @Price, @AccumulatedVol, @AccumulatedVal, @Highest, @Lowest, @Time) ON DUPLICATE KEY UPDATE StockNo=@StockNo, Price=@Price, AccumulatedVol=@AccumulatedVol, AccumulatedVal=@AccumulatedVal, Highest=@Highest, Lowest=@Lowest, Time=@Time;";

        private static string _CountLe = "SELECT COUNT(*) FROM _LE;";
        private static string _DeleteLe = "DELETE FROM _LE;";

        private static string _InsertLeCompact = "INSERT INTO _LE_COMPACT(stockno, price, volume, p1vol, p1val, p2vol, p2val, time) VALUES (@stockno, @price, @volume, @volp1, @valp1, @volp2, @valp2, @time) ON DUPLICATE KEY UPDATE stockno=@stockno, price=@price, volume=@volume, p1vol=@volp1, p1val=@valp1, p2vol=@volp2, p2val=@valp2, time=@time;";

        //private static string _InsertLeCompact = "INSERT INTO _LE_COMPACT(stockno, p1vol, p1val, p2vol, p2val) VALUES (@stockno, IFNULL((select volp1 from v_le_phase1 where stockno=@stockno),0), p1val=IFNULL((select valp1 from v_le_phase1 where stockno=@stockno),IFNULL((select valp2 from v_le_phase2 where stockno=@stockno),0)), IFNULL((select volp2 from v_le_phase2 where stockno=@stockno),0), 0) ON DUPLICATE KEY UPDATE stockno=@stockno, p1vol=IFNULL((select volp1 from v_le_phase1 where stockno=@stockno),0) , p1val=IFNULL((select valp1 from v_le_phase1 where stockno=@stockno),0), p2vol=IFNULL((select volp2 from v_le_phase2 where stockno=@stockno),0), p2val=IFNULL((select valp2 from v_le_phase2 where stockno=@stockno),0);";
        private static string _CountLeCompact = "SELECT COUNT(*) FROM _LE_COMPACT;";

        private static string _DeleteLeCompact = "DELETE FROM _LE_COMPACT;";

        private static string _InsertMarketStat = "INSERT INTO _MARKETSTAT (ControlCode, Time) VALUES (@ControlCode, @Time) ON DUPLICATE KEY UPDATE ControlCode=@ControlCode, Time=@Time;";
        private static string _CountMarketStat = "SELECT COUNT(*) FROM _MARKETSTAT;";
        private static string _DeleteMarketStat = "DELETE FROM _MARKETSTAT;";

        private static string _InsertNewList = "INSERT INTO _NEWLIST (StockNo, StockSymbol, StockType, StockName, SectorNo) VALUES (@StockNo, @StockSymbol, @StockType, @StockName, @SectorNo) ON DUPLICATE KEY UPDATE StockNo=@StockNo, StockSymbol=@StockSymbol, StockType=@StockType, StockName=@StockName, SectorNo=@SectorNo;";
        private static string _CountNewList = "SELECT COUNT(*) FROM _NEWLIST;";
        private static string _DeleteNewList = "DELETE FROM _NEWLIST;";

        private static string _InsertPutAd = "INSERT INTO _PUT_AD (TradeID, StockNo, Vol, Price, FirmNo, Side, Board, Time, Flag) VALUES (@TradeID, @StockNo, @Vol, @Price, @FirmNo, @Side, @Board, @Time, @Flag) ON DUPLICATE KEY UPDATE TradeID=@TradeID, StockNo=@StockNo, Vol=@Vol, Price=@Price, FirmNo=@FirmNo, Side=@Side, Board=@Board, Time=@Time, Flag=@Flag;";
        private static string _CountPutAd = "SELECT COUNT(*) FROM _put_ad;";
        private static string _DeletePutAd = "DELETE FROM _put_ad;";

        private static string _InsertPutDc = "INSERT INTO _PUT_DC (ConfirmNo, StockNo, Vol, Price, Board) VALUES (@ConfirmNo, @StockNo, @Vol, @Price, @Board) ON DUPLICATE KEY UPDATE ConfirmNo=@ConfirmNo, StockID=@StockID, Vol=@Vol, Price=@Price, Board=@Board;";
        private static string _CountPutDc = "SELECT COUNT(*) FROM _put_dc;";
        private static string _DeletePutDc = "DELETE FROM _put_dc;";

        private static string _InsertPutExec = "INSERT INTO _PUT_EXEC (ConfirmNo, StockID, Vol, Price, Board) VALUES (@ConfirmNo, @StockID, @Vol, @Price, @Board) ON DUPLICATE KEY UPDATE ConfirmNo=@ConfirmNo, StockID=@StockID, Vol=@Vol, Price=@Price, Board=@Board";
        private static string _CountPutExec = "SELECT COUNT(*) FROM _put_exec;";
        private static string _DeletePutExec = "DELETE FROM _put_exec;";

        private static string _InsertSecurities = "INSERT INTO _SECURITIES (StockNo, StockSymbol, StockType, Ceiling, Floor, BigLotValue, SecuritiesName, SectorNo, Designated, Suspension, Delist, HaltResumeFlag, Split, Benefit, Meeting, Notice, ClientIDRequest, CouponRate, IssueDate, MatureDate, AvrPrice, ParValue, SDCFlag, PriorClosePrice, PriorCloseDate, ProjectOpen, OpenPrice, Last, LastVol, LastVal, Highest, Lowest, TotalShare, TotalValue, AccumulateDeal, BigDeal, BigVol, BigVal, OddVol, OddVal,  Best3Bid, Best3BidVolume, Best2Bid, Best2BidVolume, Best1Bid, Best1BidVolume, Best1Offer, Best1OfferVolume, Best2Offer, Best2OfferVolume, Best3Offer, Best3OfferVolume, BoardLost) VALUES (@StockNo, @StockSymbol, @StockType, @Ceiling, @Floor, @BigLotValue, @SecuritiesName, @SectorNo, @Designated, @Suspension, @Delist, @HaltResumeFlag, @Split, @Benefit, @Meeting, @Notice, @ClientIDRequest, @CouponRate, @IssueDate, @MatureDate, @AvrPrice, @ParValue, @SDCFlag, @PriorClosePrice, @PriorCloseDate, @ProjectOpen, @OpenPrice, @Last, @LastVol, @LastVal, @Highest, @Lowest, @TotalShare, @TotalValue, @AccumulateDeal, @BigDeal, @BigVol, @BigVal, @OddVol, @OddVal, @Best3Bid, @Best3BidVolume, @Best2Bid, @Best2BidVolume, @Best1Bid, @Best1BidVolume, @Best1Offer, @Best1OfferVolume, @Best2Offer, @Best2OfferVolume, @Best3Offer, @Best3OfferVolume, @BoardLost);";
        private static string _CountSecurities = "SELECT COUNT(*) FROM _SECURITIES;";
        private static string _DeleteSecurities = "DELETE FROM _SECURITIES;";
        private static string _UpdateSecuritiesByStockSymbol = "UPDATE _SECURITIES SET              Designated = @Designated, Suspension = @Suspension, Delist = @Delist, HaltResumeFlag = @HaltResumeFlag, Split = @Split, Benefit = @Benefit, Meeting = @Meeting, Notice = @Notice, ClientIDRequest = @ClientIDRequest, CouponRate = @CouponRate, IssueDate = @IssueDate, MatureDate = @MatureDate, AvrPrice = @AvrPrice, ParValue = @ParValue, SDCFlag = @SDCFlag, ProjectOpen = @ProjectOpen, Last = @Last, LastVol = @LastVol, LastVal = @LastVal, Highest = @Highest, Lowest = @Lowest, TotalShare = @TotalShare, TotalValue = @TotalValue, AccumulateDeal = @AccumulateDeal, BigDeal = @BigDeal, BigVol = @BigVol, BigVal = @BigVal, OddVol = @OddVol, OddVal = @OddVal, Best3Bid = @Best3Bid, Best3BidVolume = @Best3BidVolume, Best2Bid = @Best2Bid, Best2BidVolume = @Best2BidVolume, Best1Bid = @Best1Bid, Best1BidVolume = @Best1BidVolume, Best1Offer = @Best1Offer, Best1OfferVolume = @Best1OfferVolume, Best2Offer = @Best2Offer, Best2OfferVolume = @Best2OfferVolume, Best3Offer = @Best3Offer, Best3OfferVolume = @Best3OfferVolume, BoardLost = @BoardLost, OpenPrice = @OpenPrice WHERE     (StockSymbol = @StockSymbol)";
        private static string _SelectSecurities = "SELECT StockNo, StockSymbol, StockType, Ceiling, Floor, BigLotValue, SecuritiesName, SectorNo, Designated, Suspension, Delist, HaltResumeFlag, Split, Benefit, Meeting, Notice, ClientIDRequest, CouponRate, IssueDate, MatureDate, AvrPrice, ParValue, SDCFlag, PriorClosePrice, PriorCloseDate, ProjectOpen, OpenPrice, Last, LastVol, LastVal, Highest, Lowest, TotalShare, TotalValue, AccumulateDeal, BigDeal, BigVol, BigVal, OddVol, OddVal,  Best3Bid, Best3BidVolume, Best2Bid, Best2BidVolume, Best1Bid, Best1BidVolume, Best1Offer, Best1OfferVolume, Best2Offer, Best2OfferVolume, Best3Offer, Best3OfferVolume, BoardLost FROM _SECURITIES";

        private static string _InsertTotalMkt = "INSERT INTO _TOTALMKT (VnIndex, TotalTrade, TotalShares, TotalValues, UpVolume, NoChangeVolume, DownVolume, Advances, NoChange, Declines, VN50Index, MarketID, Filter, Time) VALUES (@VnIndex, @TotalTrade, @TotalShares, @TotalValues, @UpVolume, @NoChangeVolume, @DownVolume, @Advances, @NoChange, @Declines, @VN50Index, @MarketID, @Filter, @Time) ON DUPLICATE KEY UPDATE VnIndex=@VnIndex, TotalTrade=@TotalTrade, TotalShares=@TotalShares, TotalValues=@TotalValues, UpVolume=@UpVolume, NoChangeVolume=@NoChangeVolume, DownVolume=@DownVolume, Advances=@Advances, NoChange=@NoChange, Declines=@Declines, VN50Index=@VN50Index, MarketID=@MarketID, Filter=@Filter, Time=@Time;";
        private static string _CountTotalMkt = "SELECT COUNT(*) FROM _TOTALMKT;";
        private static string _DeleteTotalMkt = "DELETE FROM _TOTALMKT;";

        private static string _getSecuritiesNameBySymbol = "SELECT SecuritiesName FROM _SECURITIES WHERE StockSymbol=@StockSymbol;";
        private static string _getSecuritiesSymbol = "SELECT StockSymbol FROM _SECURITIES;";
        private static string _CountBackupSecurities = "SELECT COUNT(*) as RECNUM FROM BACKUP_SECURITIES where TransDate=@transDate;";

        private static string _UpdateATOByStockNo = "";
        private static string _UpdateATCByStockNo = "";
        private static string _UpdateSession2ByStockNo = "";
        private static string _InitSessionData = "";
        private static string _BackupHose = "";
        private static string _ClearHose = "DELETE FROM _LE; DELETE FROM _SECURITIES; DELETE FROM _FROOM; DELETE FROM _FROOM_ORIGIN; DELETE FROM _SECURITIES; DELETE FROM _TOTALMKT; DELETE FROM _MARKETSTAT; DELETE FROM _LE_COMPACT; DELETE FROM _SECURITIES_INFO; DELETE FROM _PUT_EXEC; DELETE FROM _PUT_AD; DELETE FROM _PUT_DC; DELETE FROM _PUT_AD; DELETE FROM _NEWLIST; DELETE FROM _DELIST;";

        private static string _UpdateSecurities1 = "INSERT INTO _SECURITIES (StockNo, StockSymbol, StockType, Ceiling, Floor, BigLotValue, SecuritiesName, SectorNo, Designated, Suspension, Delist, HaltResumeFlag, Split, Benefit, Meeting, Notice, ClientIDRequest, CouponRate, IssueDate, MatureDate, AvrPrice, ParValue, SDCFlag, PriorClosePrice, PriorCloseDate, ProjectOpen, OpenPrice, Last, LastVol, LastVal, Highest, Lowest, TotalShare, TotalValue, AccumulateDeal, BigDeal, BigVol, BigVal, OddVol, OddVal,  Best3Bid, Best3BidVolume, Best2Bid, Best2BidVolume, Best1Bid, Best1BidVolume, Best1Offer, Best1OfferVolume, Best2Offer, Best2OfferVolume, Best3Offer, Best3OfferVolume, BoardLost) VALUES (@StockNo, @StockSymbol, @StockType, @Ceiling, @Floor, @BigLotValue, @SecuritiesName, @SectorNo, @Designated, @Suspension, @Delist, @HaltResumeFlag, @Split, @Benefit, @Meeting, @Notice, @ClientIDRequest, @CouponRate, @IssueDate, @MatureDate, @AvrPrice, @ParValue, @SDCFlag, @PriorClosePrice, @PriorCloseDate, @ProjectOpen, @OpenPrice, @Last, @LastVol, @LastVal, @Highest, @Lowest, @TotalShare, @TotalValue, @AccumulateDeal, @BigDeal, @BigVol, @BigVal, @OddVol, @OddVal, @Best3Bid, @Best3BidVolume, @Best2Bid, @Best2BidVolume, @Best1Bid, @Best1BidVolume, @Best1Offer, @Best1OfferVolume, @Best2Offer, @Best2OfferVolume, @Best3Offer, @Best3OfferVolume, @BoardLost) ON DUPLICATE KEY UPDATE StockNo=@StockNo, StockSymbol=@StockSymbol, StockType=@StockType, Ceiling=@Ceiling, Floor=@Floor, BigLotValue=@BigLotValue, SecuritiesName=@SecuritiesName, SectorNo=@SectorNo, Designated=@Designated, Suspension=@Suspension, Delist=@Delist, HaltResumeFlag=@HaltResumeFlag, Split=@Split, Benefit=@Benefit, Meeting=@Meeting, Notice=@Notice, ClientIDRequest=@ClientIDRequest, CouponRate=@CouponRate, IssueDate=@IssueDate, MatureDate=@MatureDate, AvrPrice=@AvrPrice, ParValue=@ParValue, SDCFlag=@SDCFlag, PriorClosePrice=@PriorClosePrice, PriorCloseDate=@PriorCloseDate, ProjectOpen=@ProjectOpen, OpenPrice=@OpenPrice, Last=@Last, LastVol=@LastVol, LastVal=@LastVal, Highest=@Highest, Lowest=@Lowest, TotalShare=@TotalShare, TotalValue=@TotalValue, AccumulateDeal=@AccumulateDeal, BigDeal=@BigDeal, BigVol=@BigVol, BigVal=@BigVal, OddVol=@OddVol, OddVal=@OddVal, Best3Bid=@Best3Bid, Best3BidVolume=@Best3BidVolume, Best2Bid=@Best2Bid, Best2BidVolume=@Best2BidVolume, Best1Bid=@Best1Bid, Best1BidVolume=@Best1BidVolume, Best1Offer=@Best1Offer, Best1OfferVolume=@Best1OfferVolume, Best2Offer=@Best2Offer, Best2OfferVolume=@Best2OfferVolume, Best3Offer=@Best3Offer, Best3OfferVolume=@Best3OfferVolume, BoardLost=@BoardLost;";

        private static string _UpdateSecurities2 = "INSERT INTO _SECURITIES (StockNo, StockSymbol, StockType, Ceiling, Floor, BigLotValue, SecuritiesName, SectorNo, Designated, Suspension, Delist, HaltResumeFlag, Split, Benefit, Meeting, Notice, ClientIDRequest, CouponRate, IssueDate, MatureDate, AvrPrice, ParValue, SDCFlag, PriorClosePrice, PriorCloseDate, ProjectOpen, OpenPrice, Last, LastVol, LastVal, Highest, Lowest, TotalShare, TotalValue, AccumulateDeal, BigDeal, BigVol, BigVal, OddVol, OddVal,  Best3Bid, Best3BidVolume, Best2Bid, Best2BidVolume, Best1Bid, Best1BidVolume, Best1Offer, Best1OfferVolume, Best2Offer, Best2OfferVolume, Best3Offer, Best3OfferVolume, BoardLost) VALUES (@StockNo, @StockSymbol, @StockType, @Ceiling, @Floor, @BigLotValue, @SecuritiesName, @SectorNo, @Designated, @Suspension, @Delist, @HaltResumeFlag, @Split, @Benefit, @Meeting, @Notice, @ClientIDRequest, @CouponRate, @IssueDate, @MatureDate, @AvrPrice, @ParValue, @SDCFlag, @PriorClosePrice, @PriorCloseDate, @ProjectOpen, @OpenPrice, @Last, @LastVol, @LastVal, @Highest, @Lowest, @TotalShare, @TotalValue, @AccumulateDeal, @BigDeal, @BigVol, @BigVal, @OddVol, @OddVal, @Best3Bid, @Best3BidVolume, @Best2Bid, @Best2BidVolume, @Best1Bid, @Best1BidVolume, @Best1Offer, @Best1OfferVolume, @Best2Offer, @Best2OfferVolume, @Best3Offer, @Best3OfferVolume, @BoardLost) ON DUPLICATE KEY UPDATE Designated = @Designated, Suspension = @Suspension, Delist = @Delist, HaltResumeFlag = @HaltResumeFlag, Split = @Split, Benefit = @Benefit, Meeting = @Meeting, Notice = @Notice, ClientIDRequest = @ClientIDRequest, CouponRate = @CouponRate, IssueDate = @IssueDate, MatureDate = @MatureDate, AvrPrice = @AvrPrice, ParValue = @ParValue, SDCFlag = @SDCFlag, ProjectOpen = @ProjectOpen, Last = @Last, LastVol = @LastVol, LastVal = @LastVal, Highest = @Highest, Lowest = @Lowest, TotalShare = @TotalShare, TotalValue = @TotalValue, AccumulateDeal = @AccumulateDeal, BigDeal = @BigDeal, BigVol = @BigVol, BigVal = @BigVal, OddVol = @OddVol, OddVal = @OddVal, Best3Bid = @Best3Bid, Best3BidVolume = @Best3BidVolume, Best2Bid = @Best2Bid, Best2BidVolume = @Best2BidVolume, Best1Bid = @Best1Bid, Best1BidVolume = @Best1BidVolume, Best1Offer = @Best1Offer, Best1OfferVolume = @Best1OfferVolume, Best2Offer = @Best2Offer, Best2OfferVolume = @Best2OfferVolume, Best3Offer = @Best3Offer, Best3OfferVolume = @Best3OfferVolume, BoardLost = @BoardLost, OpenPrice = @OpenPrice";

        private static string _getProData = "SELECT * FROM v_prodata";

        #endregion

        #region Parameters
        private const string PARM_STOCKNO = "@STOCKNO";
        private const string PARM_STOCKID = "@STOCKID";
        private const string PARM_STOCKSYMBOL = "@STOCKSYMBOL";
        private const string PARM_STOCKTYPE = "@STOCKTYPE";
        private const string PARM_CEILING = "@CEILING";
        private const string PARM_FLOOR = "@FLOOR";
        private const string PARM_BIGLOTVALUE = "@BIGLOTVALUE";
        private const string PARM_SECURITIESNAME = "@SECURITIESNAME";
        private const string PARM_SECTORNO = "@SECTORNO";
        private const string PARM_DESIGNATED = "@DESIGNATED";
        private const string PARM_SUSPENSION = "@SUSPENSION";
        private const string PARM_DELIST = "@DELIST";
        private const string PARM_HALTRESUMEFLAG = "@HALTRESUMEFLAG";
        private const string PARM_SPLIT = "@SPLIT";
        private const string PARM_BENEFIT = "@BENEFIT";
        private const string PARM_MEETING = "@MEETING";
        private const string PARM_NOTICE = "@NOTICE";
        private const string PARM_CLIENTIDREQUEST = "@CLIENTIDREQUEST";
        private const string PARM_COUPONRATE = "@COUPONRATE";
        private const string PARM_ISSUEDATE = "@ISSUEDATE";
        private const string PARM_MATUREDATE = "@MATUREDATE";
        private const string PARM_AVRPRICE = "@AVRPRICE";
        private const string PARM_PARVALUE = "@PARVALUE";
        private const string PARM_SDCFLAG = "@SDCFLAG";
        private const string PARM_PRIORCLOSEPRICE = "@PRIORCLOSEPRICE";
        private const string PARM_PRIORCLOSEDATE = "@PRIORCLOSEDATE";
        private const string PARM_PROJECTOPEN = "@PROJECTOPEN";
        private const string PARM_OPENPRICE = "@OPENPRICE";
        private const string PARM_LAST = "@LAST";
        private const string PARM_LASTVOL = "@LASTVOL";
        private const string PARM_LASTVAL = "@LASTVAL";
        private const string PARM_HIGHEST = "@HIGHEST";
        private const string PARM_LOWEST = "@LOWEST";
        private const string PARM_TOTALSHARE = "@TOTALSHARE";
        private const string PARM_TOTALVALUE = "@TOTALVALUE";
        private const string PARM_ACCUMULATEDEAL = "@ACCUMULATEDEAL";
        private const string PARM_BIGDEAL = "@BIGDEAL";
        private const string PARM_BIGVOL = "@BIGVOL";
        private const string PARM_BIGVAL = "@BIGVAL";
        private const string PARM_TOTALVOL = "@TOTALVOL";
        private const string PARM_BEST3BID = "@BEST3BID";
        private const string PARM_BEST3BIDVOLUME = "@BEST3BIDVOLUME";
        private const string PARM_BEST2BID = "@BEST2BID";
        private const string PARM_BEST2BIDVOLUME = "@BEST2BIDVOLUME";
        private const string PARM_BEST1BID = "@BEST1BID";
        private const string PARM_BEST1BIDVOLUME = "@BEST1BIDVOLUME";
        private const string PARM_BEST1OFFER = "@BEST1OFFER";
        private const string PARM_BEST1OFFERVOLUME = "@BEST1OFFERVOLUME";
        private const string PARM_BEST2OFFER = "@BEST2OFFER";
        private const string PARM_BEST2OFFERVOLUME = "@BEST2OFFERVOLUME";
        private const string PARM_BEST3OFFER = "@BEST3OFFER";
        private const string PARM_BEST3OFFERVOLUME = "@BEST3OFFERVOLUME";
        private const string PARM_BOARDLOST = "@BOARDLOST";

        private const string PARM_TIME = "@TIME";
        private const string PARM_VNINDEX = "@VNINDEX";
        private const string PARM_TOTALTRADE = "@TOTALTRADE";
        private const string PARM_TOTALSHARES = "@TOTALSHARES";
        private const string PARM_TOTALVALUES = "@TOTALVALUES";
        private const string PARM_UPVOLUME = "@UPVOLUME";
        private const string PARM_DOWNVOLUME = "@DOWNVOLUME";
        private const string PARM_NOCHANGEVOLUME = "@NOCHANGEVOLUME";
        private const string PARM_ADVANCES = "@ADVANCES";
        private const string PARM_DECLINES = "@DECLINES";
        private const string PARM_NOCHANGE = "@NOCHANGE";
        private const string PARM_MARKET = "@MARKET";
        private const string PARM_ODDVOL = "@ODDVOL";
        private const string PARM_ODDVAL = "@ODDVAL";

        private const string PARM_TOTALROOM = "@TOTALROOM";
        private const string PARM_CURRENTROOM = "@CURRENTROOM";
        private const string PARM_CONTROLCODE = "@CONTROLCODE";
        private const string PARM_ACCUMULATEDVOL = "@ACCUMULATEDVOL";
        private const string PARM_ACCUMULATEDVAL = "@ACCUMULATEDVAL";
        private const string PARM_PRICE = "@PRICE";
        private const string PARM_CONFIRMNO = "@CONFIRMNO";
        private const string PARM_STOCKNAME = "@STOCKNAME";
        private const string PARM_BOARD = "@BOARD";
        private const string PARM_TRADEID = "@TRADEID";
        private const string PARM_FIRMNO = "@FIRMNO";
        private const string PARM_FLAG = "@FLAG";
        private const string PARM_VOL = "@VOL";
        private const string PARM_SIDE = "@SIDE";
        private const string PARM_VN50INDEX = "@VN50INDEX";
        private const string PARM_MARKETID = "@MARKETID";
        private const string PARM_FILTER = "@FILTER";
        private const string PARM_TRANSDATE = "@TRANSDATE";

        #endregion

        #region ISecurities Members

        public void InsertDeList(int stockNo, string symbol, string stockType, string stockName, int sectorNo)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int32, 2),
                                                new MySqlParameter(PARM_STOCKSYMBOL, MySqlDbType.VarChar, 8),
                                                new MySqlParameter(PARM_STOCKTYPE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_STOCKNAME, MySqlDbType.VarChar,25),
                                                new MySqlParameter(PARM_SECTORNO, MySqlDbType.Int32,2)};
                parms[0].Value = stockNo;
                parms[1].Value = symbol;
                parms[2].Value = stockType;
                parms[3].Value = stockName;
                parms[4].Value = sectorNo;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _InsertDeList, parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountDeList()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, _CountDeList));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteDeList()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _DeleteDeList);
            }
            catch
            {
                throw;
            }
        }

        public void InsertLe(long stockNo, long price, long accumulateVol, double accumulateVal, long highest, long lowest, long time)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_ACCUMULATEDVOL, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_ACCUMULATEDVAL, MySqlDbType.Float, 8),
                                                new MySqlParameter(PARM_HIGHEST, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_LOWEST, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_TIME, MySqlDbType.Int64,4)};
                parms[0].Value = stockNo;
                parms[1].Value = price;
                parms[2].Value = accumulateVol;
                parms[3].Value = accumulateVal;
                parms[4].Value = highest;
                parms[5].Value = lowest;
                parms[6].Value = time;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _InsertLe, parms);
            }
            catch
            {
                throw;
            }
        }

        public void UpdateFroom(long stockNo, double totalRoom, double currentRoom, int buy, int sell)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_TOTALROOM, MySqlDbType.Float, 8),
                                                new MySqlParameter(PARM_CURRENTROOM, MySqlDbType.Float,8),
                                                new MySqlParameter("@BUY", MySqlDbType.Int32),
                                                new MySqlParameter("@SELL", MySqlDbType.Int32),
                 };
                parms[0].Value = stockNo;
                parms[1].Value = totalRoom;
                parms[2].Value = currentRoom;
                parms[3].Value = buy;
                parms[4].Value = sell;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _UpdateFroom, parms);
            }
            catch
            {
                throw;
            }
        }
        //public List<Froom> getFroom()
        //{
        //    return null;
        //}

        public void InsertLeCompact(int stockno, int price, int volume, int volp1, int valp1, int volp2, int valp2, int time)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.Int32, 2),
                                                new MySqlParameter("@VOLUME", MySqlDbType.Int32, 2),
                                                new MySqlParameter("@volp1", MySqlDbType.Int32, 2),
                                                new MySqlParameter("@valp1", MySqlDbType.Int32, 2),
                                                new MySqlParameter("@volp2", MySqlDbType.Int32, 2),
                                                new MySqlParameter("@valp2", MySqlDbType.Int32, 2),
                                                new MySqlParameter("@time", MySqlDbType.Int32, 2)};
                parms[0].Value = stockno;
                parms[1].Value = price;
                parms[2].Value = volume;
                parms[3].Value = volp1;
                parms[4].Value = valp1;
                parms[5].Value = volp2;
                parms[6].Value = valp2;
                parms[7].Value = time;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _InsertLeCompact, parms);
            }
            catch
            {
                throw;
            }
        }


        public int CountLe()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, _CountLe));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteLe()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _DeleteLe);
            }
            catch
            {
                throw;
            }
        }

        public void InsertMarketStat(string controlCode, long time)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_CONTROLCODE, MySqlDbType.VarChar, 4),
                                                new MySqlParameter(PARM_TIME, MySqlDbType.Int64, 8)};
                parms[0].Value = controlCode;
                parms[1].Value = time;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _InsertMarketStat, parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountMarketStat()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, _CountMarketStat));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteMarketStat()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _DeleteMarketStat);
            }
            catch
            {
                throw;
            }
        }

        public void InsertNewList(int stockNo, string symbol, string stockType, string stockName, int sectorNo)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int32, 2),
                                                new MySqlParameter(PARM_STOCKSYMBOL, MySqlDbType.VarChar, 8),
                                                new MySqlParameter(PARM_STOCKTYPE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_STOCKNAME, MySqlDbType.VarChar,25),
                                                new MySqlParameter(PARM_SECTORNO, MySqlDbType.Int32,2)};
                parms[0].Value = stockNo;
                parms[1].Value = symbol;
                parms[2].Value = stockType;
                parms[3].Value = stockName;
                parms[4].Value = sectorNo;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _InsertNewList, parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountNewList()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, _CountNewList));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteNewList()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _DeleteNewList);
            }
            catch
            {
                throw;
            }
        }

        public void InsertPutAd(int tradeid, int stockNo, long vol, double price, long firmno, string side, string board, long time, string flag)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TRADEID, MySqlDbType.Int32, 2),
                                                new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int32, 2),
                                                new MySqlParameter(PARM_VOL, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_FIRMNO, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_SIDE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_BOARD, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_TIME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_FLAG, MySqlDbType.VarChar,1)};
                parms[0].Value = tradeid;
                parms[1].Value = stockNo;
                parms[2].Value = vol;
                parms[3].Value = price;
                parms[4].Value = firmno;
                parms[5].Value = side;
                parms[6].Value = board;
                parms[7].Value = time;
                parms[8].Value = flag;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _InsertPutAd, parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountPutAd()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, _CountPutAd));
            }
            catch
            {
                throw;
            }
        }

        public void DeletePutAd()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _DeletePutAd);
            }
            catch
            {
                throw;
            }
        }

        public void InsertPutDc(int confirmNo, int stockNo, long vol, long price, string board)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_CONFIRMNO, MySqlDbType.Int32, 2),
                                                new MySqlParameter(PARM_STOCKNO, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_VOL, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_BOARD, MySqlDbType.Int32,1)};
                parms[0].Value = confirmNo;
                parms[1].Value = stockNo;
                parms[2].Value = vol;
                parms[3].Value = price;
                parms[4].Value = board;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _InsertPutDc, parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountPutDc()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, _CountPutDc));
            }
            catch
            {
                throw;
            }
        }

        public void DeletePutDc()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _DeletePutDc);
            }
            catch
            {
                throw;
            }
        }

        public void InsertPutExec(long confirmNo, int stockNo, long vol, long price, string board)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_CONFIRMNO, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_STOCKID, MySqlDbType.Int32, 2),
                                                new MySqlParameter(PARM_VOL, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BOARD, MySqlDbType.VarChar,1)};
                parms[0].Value = confirmNo;
                parms[1].Value = stockNo;
                parms[2].Value = vol;
                parms[3].Value = price;
                parms[4].Value = board;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _InsertPutExec, parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountPutExec()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, _CountPutExec));
            }
            catch
            {
                throw;
            }
        }

        public void DeletePutExec()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _DeletePutExec);
            }
            catch
            {
                throw;
            }
        }

        public void InsertSecurities(int stockNo, string symbol, string stockType, long ceiling, long floor, double bigLotValue, string securitiesName, string sectorNo, string designated, string suspension, string delist, string haltResumeFlag, string split, string benefit, string meeting, string notice, string clientIdRequest, int couponRate, string issueDate, string matureDate, long avrPrice, int parValue, string SDCFlag, long PriorClosePrice, string priorCloseDate, long projectOpen, long openPrice, long last, long lastVol, double lastVal, long highest, long lowest, double totalShare, double totalValue, int AccumulateDeal, int bigDeal, long bigVol, double bigVal, long oddVol, double oddVal, long best3Bid, long best3BidVolume, long best2Bid, long best2BidVolume, long best1Bid, long best1BidVolume, long best1Offer, long best1OfferVolume, long best2Offer, long best2OfferVolume, long best3Offer, long best3OfferVolume, int boardLost)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int32, 2),
                                                new MySqlParameter(PARM_STOCKSYMBOL, MySqlDbType.VarChar, 8),
                                                new MySqlParameter(PARM_STOCKTYPE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_CEILING, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_FLOOR, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BIGLOTVALUE, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_SECURITIESNAME, MySqlDbType.VarChar,25),
                                                new MySqlParameter(PARM_SECTORNO, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_DESIGNATED, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_SUSPENSION, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_DELIST, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_HALTRESUMEFLAG, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_SPLIT, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_BENEFIT, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_MEETING, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_NOTICE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_CLIENTIDREQUEST, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_COUPONRATE, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_ISSUEDATE, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_MATUREDATE, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_AVRPRICE, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_PARVALUE, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_SDCFLAG, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_PRIORCLOSEPRICE, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_PRIORCLOSEDATE, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_PROJECTOPEN, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_OPENPRICE, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_LAST, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_LASTVOL, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_LASTVAL, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_HIGHEST, MySqlDbType.Int32,4),
                                                new MySqlParameter(PARM_LOWEST, MySqlDbType.Int32,4),
                                                new MySqlParameter(PARM_TOTALSHARE, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_TOTALVALUE, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_ACCUMULATEDEAL, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_BIGDEAL, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_BIGVOL, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BIGVAL, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_ODDVOL, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_ODDVAL, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_BEST1BID, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST1BIDVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST2BID, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST2BIDVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST3BID, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST3BIDVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST1OFFER, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST1OFFERVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST2OFFER, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST2OFFERVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST3OFFER, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST3OFFERVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BOARDLOST, MySqlDbType.Int32,2)};
                parms[0].Value = stockNo;
                parms[1].Value = symbol;
                parms[2].Value = stockType;
                parms[3].Value = ceiling;
                parms[4].Value = floor;
                parms[5].Value = bigLotValue;
                parms[6].Value = securitiesName;
                parms[7].Value = sectorNo;
                parms[8].Value = designated;
                parms[9].Value = suspension;
                parms[10].Value = delist;
                parms[11].Value = haltResumeFlag;
                parms[12].Value = split;
                parms[13].Value = benefit;
                parms[14].Value = meeting;
                parms[15].Value = notice;
                parms[16].Value = clientIdRequest;
                parms[17].Value = couponRate;
                parms[18].Value = issueDate;
                parms[19].Value = matureDate;
                parms[20].Value = avrPrice;
                parms[21].Value = parValue;
                parms[22].Value = SDCFlag;
                parms[23].Value = PriorClosePrice;
                parms[24].Value = priorCloseDate;
                parms[25].Value = projectOpen;
                parms[26].Value = openPrice;
                parms[27].Value = last;
                parms[28].Value = lastVol;
                parms[29].Value = lastVal;
                parms[30].Value = highest;
                parms[31].Value = lowest;
                parms[32].Value = totalShare;
                parms[33].Value = totalValue;
                parms[34].Value = AccumulateDeal;
                parms[35].Value = bigDeal;
                parms[36].Value = bigVol;
                parms[37].Value = bigVal;
                parms[38].Value = oddVol;
                parms[39].Value = oddVal;
                parms[40].Value = best1Bid;
                parms[41].Value = best1BidVolume;
                parms[42].Value = best2Bid;
                parms[43].Value = best2BidVolume;
                parms[44].Value = best3Bid;
                parms[45].Value = best3BidVolume;
                parms[46].Value = best1Offer;
                parms[47].Value = best1OfferVolume;
                parms[48].Value = best2Offer;
                parms[49].Value = best2OfferVolume;
                parms[50].Value = best3Offer;
                parms[51].Value = best3OfferVolume;
                parms[52].Value = boardLost;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _InsertSecurities, parms);
            }
            catch
            {
                throw;
            }
        }

        public void UpdateSecuritiesEarly(int stockNo, string symbol, string stockType, long ceiling, long floor, double bigLotValue, string securitiesName, string sectorNo, string designated, string suspension, string delist, string haltResumeFlag, string split, string benefit, string meeting, string notice, string clientIdRequest, int couponRate, string issueDate, string matureDate, long avrPrice, int parValue, string SDCFlag, long PriorClosePrice, string priorCloseDate, long projectOpen, long openPrice, long last, long lastVol, double lastVal, long highest, long lowest, double totalShare, double totalValue, int AccumulateDeal, int bigDeal, long bigVol, double bigVal, long oddVol, double oddVal, long best3Bid, long best3BidVolume, long best2Bid, long best2BidVolume, long best1Bid, long best1BidVolume, long best1Offer, long best1OfferVolume, long best2Offer, long best2OfferVolume, long best3Offer, long best3OfferVolume, int boardLost)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int32, 2),
                                                new MySqlParameter(PARM_STOCKSYMBOL, MySqlDbType.VarChar, 8),
                                                new MySqlParameter(PARM_STOCKTYPE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_CEILING, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_FLOOR, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BIGLOTVALUE, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_SECURITIESNAME, MySqlDbType.VarChar,25),
                                                new MySqlParameter(PARM_SECTORNO, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_DESIGNATED, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_SUSPENSION, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_DELIST, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_HALTRESUMEFLAG, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_SPLIT, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_BENEFIT, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_MEETING, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_NOTICE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_CLIENTIDREQUEST, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_COUPONRATE, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_ISSUEDATE, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_MATUREDATE, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_AVRPRICE, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_PARVALUE, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_SDCFLAG, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_PRIORCLOSEPRICE, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_PRIORCLOSEDATE, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_PROJECTOPEN, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_OPENPRICE, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_LAST, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_LASTVOL, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_LASTVAL, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_HIGHEST, MySqlDbType.Int32,4),
                                                new MySqlParameter(PARM_LOWEST, MySqlDbType.Int32,4),
                                                new MySqlParameter(PARM_TOTALSHARE, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_TOTALVALUE, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_ACCUMULATEDEAL, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_BIGDEAL, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_BIGVOL, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BIGVAL, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_ODDVOL, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_ODDVAL, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_BEST1BID, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST1BIDVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST2BID, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST2BIDVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST3BID, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST3BIDVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST1OFFER, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST1OFFERVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST2OFFER, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST2OFFERVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST3OFFER, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST3OFFERVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BOARDLOST, MySqlDbType.Int32,2)};
                parms[0].Value = stockNo;
                parms[1].Value = symbol;
                parms[2].Value = stockType;
                parms[3].Value = ceiling;
                parms[4].Value = floor;
                parms[5].Value = bigLotValue;
                parms[6].Value = securitiesName;
                parms[7].Value = sectorNo;
                parms[8].Value = designated;
                parms[9].Value = suspension;
                parms[10].Value = delist;
                parms[11].Value = haltResumeFlag;
                parms[12].Value = split;
                parms[13].Value = benefit;
                parms[14].Value = meeting;
                parms[15].Value = notice;
                parms[16].Value = clientIdRequest;
                parms[17].Value = couponRate;
                parms[18].Value = issueDate;
                parms[19].Value = matureDate;
                parms[20].Value = avrPrice;
                parms[21].Value = parValue;
                parms[22].Value = SDCFlag;
                parms[23].Value = PriorClosePrice;
                parms[24].Value = priorCloseDate;
                parms[25].Value = projectOpen;
                parms[26].Value = openPrice;
                parms[27].Value = last;
                parms[28].Value = lastVol;
                parms[29].Value = lastVal;
                parms[30].Value = highest;
                parms[31].Value = lowest;
                parms[32].Value = totalShare;
                parms[33].Value = totalValue;
                parms[34].Value = AccumulateDeal;
                parms[35].Value = bigDeal;
                parms[36].Value = bigVol;
                parms[37].Value = bigVal;
                parms[38].Value = oddVol;
                parms[39].Value = oddVal;
                parms[40].Value = best1Bid;
                parms[41].Value = best1BidVolume;
                parms[42].Value = best2Bid;
                parms[43].Value = best2BidVolume;
                parms[44].Value = best3Bid;
                parms[45].Value = best3BidVolume;
                parms[46].Value = best1Offer;
                parms[47].Value = best1OfferVolume;
                parms[48].Value = best2Offer;
                parms[49].Value = best2OfferVolume;
                parms[50].Value = best3Offer;
                parms[51].Value = best3OfferVolume;
                parms[52].Value = boardLost;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _UpdateSecurities1, parms);
            }
            catch
            {
                throw;
            }
        }

        public void UpdateSecuritiesLately(int stockNo, string symbol, string stockType, long ceiling, long floor, double bigLotValue, string securitiesName, string sectorNo, string designated, string suspension, string delist, string haltResumeFlag, string split, string benefit, string meeting, string notice, string clientIdRequest, int couponRate, string issueDate, string matureDate, long avrPrice, int parValue, string SDCFlag, long PriorClosePrice, string priorCloseDate, long projectOpen, long openPrice, long last, long lastVol, double lastVal, long highest, long lowest, double totalShare, double totalValue, int AccumulateDeal, int bigDeal, long bigVol, double bigVal, long oddVol, double oddVal, long best3Bid, long best3BidVolume, long best2Bid, long best2BidVolume, long best1Bid, long best1BidVolume, long best1Offer, long best1OfferVolume, long best2Offer, long best2OfferVolume, long best3Offer, long best3OfferVolume, int boardLost)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int32, 2),
                                                new MySqlParameter(PARM_STOCKSYMBOL, MySqlDbType.VarChar, 8),
                                                new MySqlParameter(PARM_STOCKTYPE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_CEILING, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_FLOOR, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BIGLOTVALUE, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_SECURITIESNAME, MySqlDbType.VarChar,25),
                                                new MySqlParameter(PARM_SECTORNO, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_DESIGNATED, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_SUSPENSION, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_DELIST, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_HALTRESUMEFLAG, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_SPLIT, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_BENEFIT, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_MEETING, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_NOTICE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_CLIENTIDREQUEST, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_COUPONRATE, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_ISSUEDATE, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_MATUREDATE, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_AVRPRICE, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_PARVALUE, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_SDCFLAG, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_PRIORCLOSEPRICE, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_PRIORCLOSEDATE, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_PROJECTOPEN, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_OPENPRICE, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_LAST, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_LASTVOL, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_LASTVAL, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_HIGHEST, MySqlDbType.Int32,4),
                                                new MySqlParameter(PARM_LOWEST, MySqlDbType.Int32,4),
                                                new MySqlParameter(PARM_TOTALSHARE, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_TOTALVALUE, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_ACCUMULATEDEAL, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_BIGDEAL, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_BIGVOL, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BIGVAL, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_ODDVOL, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_ODDVAL, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_BEST1BID, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST1BIDVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST2BID, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST2BIDVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST3BID, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST3BIDVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST1OFFER, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST1OFFERVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST2OFFER, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST2OFFERVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BEST3OFFER, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_BEST3OFFERVOLUME, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_BOARDLOST, MySqlDbType.Int32,2)};
                parms[0].Value = stockNo;
                parms[1].Value = symbol;
                parms[2].Value = stockType;
                parms[3].Value = ceiling;
                parms[4].Value = floor;
                parms[5].Value = bigLotValue;
                parms[6].Value = securitiesName;
                parms[7].Value = sectorNo;
                parms[8].Value = designated;
                parms[9].Value = suspension;
                parms[10].Value = delist;
                parms[11].Value = haltResumeFlag;
                parms[12].Value = split;
                parms[13].Value = benefit;
                parms[14].Value = meeting;
                parms[15].Value = notice;
                parms[16].Value = clientIdRequest;
                parms[17].Value = couponRate;
                parms[18].Value = issueDate;
                parms[19].Value = matureDate;
                parms[20].Value = avrPrice;
                parms[21].Value = parValue;
                parms[22].Value = SDCFlag;
                parms[23].Value = PriorClosePrice;
                parms[24].Value = priorCloseDate;
                parms[25].Value = projectOpen;
                parms[26].Value = openPrice;
                parms[27].Value = last;
                parms[28].Value = lastVol;
                parms[29].Value = lastVal;
                parms[30].Value = highest;
                parms[31].Value = lowest;
                parms[32].Value = totalShare;
                parms[33].Value = totalValue;
                parms[34].Value = AccumulateDeal;
                parms[35].Value = bigDeal;
                parms[36].Value = bigVol;
                parms[37].Value = bigVal;
                parms[38].Value = oddVol;
                parms[39].Value = oddVal;
                parms[40].Value = best1Bid;
                parms[41].Value = best1BidVolume;
                parms[42].Value = best2Bid;
                parms[43].Value = best2BidVolume;
                parms[44].Value = best3Bid;
                parms[45].Value = best3BidVolume;
                parms[46].Value = best1Offer;
                parms[47].Value = best1OfferVolume;
                parms[48].Value = best2Offer;
                parms[49].Value = best2OfferVolume;
                parms[50].Value = best3Offer;
                parms[51].Value = best3OfferVolume;
                parms[52].Value = boardLost;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _UpdateSecurities2, parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountSecurities()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, _CountSecurities));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteSecurities()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _DeleteSecurities);
            }
            catch
            {
                throw;
            }
        }

        public void UpdateSecuritiesByStockSymbol(string designated, string suspension, string delist, string haltResumeFlag, string split, string benefit, string meeting, string notice, string clientIdRequest, int couponRate, string issueDate, string matureDate, long avrPrice, int parValue, string SDCFlag, long projectOpen, long last, long lastVol, double lastVal, long highest, long lowest, double totalShare, double totalValue, int AccumulateDeal, int bigDeal, long bigVol, double bigVal, long oddVol, double oddVal, long best3Bid, long best3BidVolume, long best2Bid, long best2BidVolume, long best1Bid, long best1BidVolume, long best1Offer, long best1OfferVolume, long best2Offer, long best2OfferVolume, long best3Offer, long best3OfferVolume, int boardLost, long openPrice, string symbol)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_DESIGNATED, MySqlDbType.VarChar,1),
                                        new MySqlParameter(PARM_SUSPENSION, MySqlDbType.VarChar,1),
                                        new MySqlParameter(PARM_DELIST, MySqlDbType.VarChar,1),
                                        new MySqlParameter(PARM_HALTRESUMEFLAG, MySqlDbType.VarChar,1),
                                        new MySqlParameter(PARM_SPLIT, MySqlDbType.VarChar,1),
                                        new MySqlParameter(PARM_BENEFIT, MySqlDbType.VarChar,1),
                                        new MySqlParameter(PARM_MEETING, MySqlDbType.VarChar,1),
                                        new MySqlParameter(PARM_NOTICE, MySqlDbType.VarChar,1),
                                        new MySqlParameter(PARM_CLIENTIDREQUEST, MySqlDbType.VarChar,1),
                                        new MySqlParameter(PARM_COUPONRATE, MySqlDbType.Int32,2),
                                        new MySqlParameter(PARM_ISSUEDATE, MySqlDbType.VarChar,6),
                                        new MySqlParameter(PARM_MATUREDATE, MySqlDbType.VarChar,6),
                                        new MySqlParameter(PARM_AVRPRICE, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_PARVALUE, MySqlDbType.Int32,2),
                                        new MySqlParameter(PARM_SDCFLAG, MySqlDbType.VarChar,1),

                                        new MySqlParameter(PARM_PROJECTOPEN, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_LAST, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_LASTVOL, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_LASTVAL, MySqlDbType.Float,8),
                                        new MySqlParameter(PARM_HIGHEST, MySqlDbType.Int32,4),
                                        new MySqlParameter(PARM_LOWEST, MySqlDbType.Int32,4),
                                        new MySqlParameter(PARM_TOTALSHARE, MySqlDbType.Float,8),
                                        new MySqlParameter(PARM_TOTALVALUE, MySqlDbType.Float,8),
                                        new MySqlParameter(PARM_ACCUMULATEDEAL, MySqlDbType.Int32,2),
                                        new MySqlParameter(PARM_BIGDEAL, MySqlDbType.Int32,2),
                                        new MySqlParameter(PARM_BIGVOL, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_BIGVAL, MySqlDbType.Float,8),
                                        new MySqlParameter(PARM_ODDVOL, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_ODDVAL, MySqlDbType.Float,8),
                                        new MySqlParameter(PARM_BEST1BID, MySqlDbType.Int64, 4),
                                        new MySqlParameter(PARM_BEST1BIDVOLUME, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_BEST2BID, MySqlDbType.Int64, 4),
                                        new MySqlParameter(PARM_BEST2BIDVOLUME, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_BEST3BID, MySqlDbType.Int64, 4),
                                        new MySqlParameter(PARM_BEST3BIDVOLUME, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_BEST1OFFER, MySqlDbType.Int64, 4),
                                        new MySqlParameter(PARM_BEST1OFFERVOLUME, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_BEST2OFFER, MySqlDbType.Int64, 4),
                                        new MySqlParameter(PARM_BEST2OFFERVOLUME, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_BEST3OFFER, MySqlDbType.Int64, 4),
                                        new MySqlParameter(PARM_BEST3OFFERVOLUME, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_BOARDLOST, MySqlDbType.Int32,2),
                                        new MySqlParameter(PARM_OPENPRICE, MySqlDbType.Int64,4),
                                        new MySqlParameter(PARM_STOCKSYMBOL, MySqlDbType.VarChar, 8)
};
                parms[0].Value = designated;
                parms[1].Value = suspension;
                parms[2].Value = delist;
                parms[3].Value = haltResumeFlag;
                parms[4].Value = split;
                parms[5].Value = benefit;
                parms[6].Value = meeting;
                parms[7].Value = notice;
                parms[8].Value = clientIdRequest;
                parms[9].Value = couponRate;
                parms[10].Value = issueDate;
                parms[11].Value = matureDate;
                parms[12].Value = avrPrice;
                parms[13].Value = parValue;
                parms[14].Value = SDCFlag;
                parms[15].Value = projectOpen;
                parms[16].Value = last;
                parms[17].Value = lastVol;
                parms[18].Value = lastVal;
                parms[19].Value = highest;
                parms[20].Value = lowest;
                parms[21].Value = totalShare;
                parms[22].Value = totalValue;
                parms[23].Value = AccumulateDeal;
                parms[24].Value = bigDeal;
                parms[25].Value = bigVol;
                parms[26].Value = bigVal;
                parms[27].Value = oddVol;
                parms[28].Value = oddVal;
                parms[29].Value = best1Bid;
                parms[30].Value = best1BidVolume;
                parms[31].Value = best2Bid;
                parms[32].Value = best2BidVolume;
                parms[33].Value = best3Bid;
                parms[34].Value = best3BidVolume;
                parms[35].Value = best1Offer;
                parms[36].Value = best1OfferVolume;
                parms[37].Value = best2Offer;
                parms[38].Value = best2OfferVolume;
                parms[39].Value = best3Offer;
                parms[40].Value = best3OfferVolume;
                parms[41].Value = boardLost;
                parms[42].Value = openPrice;
                parms[43].Value = symbol;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _UpdateSecuritiesByStockSymbol, parms);
            }
            catch
            {
                throw;
            }
        }



        //public List<Securities> SelectSecurities()
        //{
        //    List<Securities> list = null;
        //    try
        //    {
        //        MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, _SelectSecurities);
        //        while (rdr.Read())
        //        {
        //            list.Add(new Securities());
        //        }
        //        rdr.Close();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}

        public void InsertTotalMkt(long vnIndex, long totalTrade, double totalShares, double totalVlues, double upVolume, double noChangeVolume, double downVolume, int advances, int declines, int noChange, long vn50index, string marketid, string filter, long time)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_VNINDEX, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_TOTALTRADE, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_TOTALSHARES, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_TOTALVALUES, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_UPVOLUME, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_NOCHANGEVOLUME, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_DOWNVOLUME, MySqlDbType.Float,8),
                                                new MySqlParameter(PARM_ADVANCES, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_NOCHANGE, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_DECLINES, MySqlDbType.Int32,2),
                                                new MySqlParameter(PARM_VN50INDEX, MySqlDbType.Int64,4),
                                                new MySqlParameter(PARM_MARKETID, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_FILTER, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_TIME, MySqlDbType.Int64,4)};
                parms[0].Value = vnIndex;
                parms[1].Value = totalTrade;
                parms[2].Value = totalShares;
                parms[3].Value = totalVlues;
                parms[4].Value = upVolume;
                parms[5].Value = noChangeVolume;
                parms[6].Value = downVolume;
                parms[7].Value = advances;
                parms[8].Value = noChange;
                parms[9].Value = declines;
                parms[10].Value = vn50index;
                parms[11].Value = marketid;
                parms[12].Value = filter;
                parms[13].Value = time;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _InsertTotalMkt, parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountTotalMkt()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, _CountTotalMkt));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteTotalMkt()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _DeleteTotalMkt);
            }
            catch
            {
                throw;
            }
        }

        #endregion

        public string getSecuritiesNameBySymbol(string symbol)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_STOCKSYMBOL, MySqlDbType.VarChar, 8);
                parm1.Value = symbol;

                MySqlDataReader rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, _getSecuritiesNameBySymbol, parm1);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return rdr["SecuritiesName"].ToString();
                }
                else
                    return String.Empty;
            }
            catch
            {
                throw;
            }
        }
        public List<string> getSecuritiesSymbol()
        {
            try
            {
                List<string> list = new List<string>();

                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, _getSecuritiesSymbol);
                while (rdr.Read())
                {
                    list.Add(rdr["StockSymbol"].ToString().Trim());
                }
                return list;
            }
            catch
            {
                throw;
            }
        }
        public int CountBackupSecurities(DateTime transDate)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_TRANSDATE, MySqlDbType.DateTime);
                parm1.Value = transDate;

                MySqlDataReader rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, _CountBackupSecurities, parm1);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return Convert.ToInt32(rdr["RECNUM"]);
                }
                else
                    return 0;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Works on transaction date only
        /// </summary>
        public void Backup()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _BackupHose);
            }
            catch
            {
                throw;
            }
        }
        public void Clear()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _ClearHose);
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Update the session table. Create and update each session information 
        /// </summary>
        /// <param name="stockNo"></param>
        /// <param name="transDate"></param>
        public void UpdateATOByStockNo(int stockNo, DateTime transDate)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int32);
                parm1.Value = stockNo;
                MySqlParameter parm2 = new MySqlParameter(PARM_TRANSDATE, MySqlDbType.DateTime);
                parm2.Value = transDate;
                MySqlParameter[] parms = { parm1, parm2 };

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _UpdateATOByStockNo, parms);
            }
            catch
            {
                throw;
            }
        }
        public void UpdateATCByStockNo(int stockNo, DateTime transDate)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int32);
                parm1.Value = stockNo;
                MySqlParameter parm2 = new MySqlParameter(PARM_TRANSDATE, MySqlDbType.DateTime);
                parm2.Value = transDate;
                MySqlParameter[] parms = { parm1, parm2 };

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _UpdateATCByStockNo, parms);
            }
            catch
            {
                throw;
            }
        }
        public void UpdateSession2ByStockNo(int stockNo, DateTime transDate)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int32);
                parm1.Value = stockNo;
                MySqlParameter parm2 = new MySqlParameter(PARM_TRANSDATE, MySqlDbType.DateTime);
                parm2.Value = transDate;
                MySqlParameter[] parms = { parm1, parm2 };

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _UpdateATCByStockNo, parms);
            }
            catch
            {
                throw;
            }
        }
        public void InitSessionData(DateTime transDate)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_TRANSDATE, MySqlDbType.DateTime);
                parm1.Value = transDate;

                MyHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, _InitSessionData, parm1);
            }
            catch
            {
                throw;
            }
        }

//         public List<ProData> getFullInfo()
//         {
//             try
//             {
//                 List<ProData> list = new List<ProData>();
//                 MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, _getProData);
//                 while (rdr.Read())
//                 {
//                     //list.Add(new ProDta(rdr["stocksymbol"].ToString()),
//                 }
//                 return null;
//             }
//             catch
//             {
//                 throw;
//             }
//         }

        #region ISecurities Members


        public void InsertFroomOrigin(long stockNo, double totalRoom, double currentRoom)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_STOCKNO, MySqlDbType.Int64, 4),
                                                new MySqlParameter(PARM_TOTALROOM, MySqlDbType.Float, 8),
                                                new MySqlParameter(PARM_CURRENTROOM, MySqlDbType.Float,8)};
                parms[0].Value = stockNo;
                parms[1].Value = totalRoom;
                parms[2].Value = currentRoom;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _InsertFroomOrigin, parms);
            }
            catch
            {
                throw;
            }
        }

        public int CountFroomOrigin()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, _CountFroomOrigin));
            }
            catch
            {
                throw;
            }
        }

        public void DeleteFroom()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _DeleteFroom);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteFroomOrigin()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, _DeleteFroomOrigin);
            }
            catch
            {
                throw;
            }
        }

        #endregion
    }
}

